package com.xinyirun.scm.core.system.mapper.business.rpd;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xinyirun.scm.bean.entity.business.rpd.RProductDailyAEntity;
import com.xinyirun.scm.bean.system.vo.business.rpd.BProductDailyExportVo;
import com.xinyirun.scm.bean.system.vo.business.rpd.BProductDailyVo;
import com.xinyirun.scm.common.constant.DictConstant;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

/**
 * <p>
 * 稻谷 加工日报表 Mapper 接口
 * </p>
 *
 * @author xinyirun
 * @since 2023-05-16
 */
@Repository
public interface RProductDailyAMapper extends BaseMapper<RProductDailyAEntity> {

    @Select(""
            + " SELECT                                                                                                  "
            + "   1                                                                                                     "
            + " FROM                                                                                                    "
            + "   r_product_daily_a                                                                                     "
            + " WHERE date_format(date, '%Y%m%d') = date_format(#{p1.date}, '%Y%m%d')                                        "
            + " FOR UPDATE;                                                                                             "
            )
    List<Integer> lockR_product_daily_a_10(@Param("p1") BProductDailyVo vo);

    @Delete(""
            + " DELETE FROM                                                                                             "
            + "   r_product_daily_a                                                                                     "
            + " WHERE date_format(date, '%Y%m%d') = date_format(#{p1.date}, '%Y%m%d')                                        "
//            + " AND (date_format(date, '%Y-%m-%d') >= #{p1.p00000128Vo.init_time} or #{p1.p00000128Vo.init_time} is null)"
//            + " AND (date_format(date, '%Y-%m-%d') <= #{p1.end_time} or #{p1.end_time} is null)                      "
    )
    void deleteR_product_daily_a_21(@Param("p1") BProductDailyVo vo);

    @Insert(""
            +  "  INSERT INTO r_product_daily_a ( date, warehouse_id, in_qty, product_qty, out_qty, inventory_qty, c_time ) SELECT"
            +  "  tab1.date,                                                                                            "
            +  "  tab1.warehouse_id,                                                                                    "
            +  "  tab2.in_qty,                                                                                          "
            +  "  tab3.product_qty,                                                                                     "
            +  "  tab4.out_qty,                                                                                         "
            +  "  tab5.inventory_qty,                                                                                   "
            +  "  now()                                                                                                 "
            +  "  FROM                                                                                                  "
            +  "  	(                                                                                                   "
            +  "  	SELECT                                                                                              "
            +  "  		t.id warehouse_id,                                                                              "
            +  "  		t1.date                                                                                         "
            +  "  	FROM                                                                                                "
            +  "  		m_warehouse t,                                                                                  "
            +  "  		s_calendar t1                                                                                   "
            +  "  	WHERE                                                                                               "
            +  "        date_format(date, '%Y%m%d') = date_format(#{p1.date}, '%Y%m%d')                                      "
//            +  "  		AND (date_format(date, '%Y-%m-%d') >= #{p1.start_time} or #{p1.start_time} is null)             "
//            +  "  		AND (date_format(date, '%Y-%m-%d') <= #{p1.end_time} or #{p1.end_time} is null)                 "
            +  "  		AND t.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                            "
            +  "  		AND t.`enable` = '1'                                                                            "
            +  "  	) tab1                                                                                              "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		SUM( t.actual_weight ) in_qty,                                                                  "
            +  "  		t.e_dt_date date,                                                                               "
            +  "  		t.warehouse_id                                                                                  "
            +  "  	FROM                                                                                                "
            +  "  		b_in t                                                                                          "
            +  "  		LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "  		LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                              "
            +  "  	WHERE                                                                                               "
//            +  "  		t.type = '"+ DictConstant.DICT_B_IN_TYPE_JG +"'                                                 "
            +  "  		AND t.`status` = '2'                                                                            "
            +  "  		AND t1.goods_code = #{p1.p00000128Vo.column_one}                                                            "
            +  "  		AND t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                           "
            +  "  		AND t2.`enable` = '1'                                                                           "
            +  "  	GROUP BY                                                                                            "
            +  "  		t.warehouse_id,                                                                                 "
            +  "  		e_dt_date                                                                                       "
            +  "  	) tab2 ON tab1.warehouse_id = tab2.warehouse_id                                                     "
            +  "  	AND tab1.date = tab2.date                                                                           "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		SUM( t.actual_weight ) product_qty,                                                              "
            +  "  		DATE_FORMAT( t.e_dt, '%Y-%m-%d' ) date,                                                         "
            +  "  		t.warehouse_id                                                                                  "
            +  "  	FROM                                                                                                "
            +  "  		b_out t                                                                                         "
            +  "  		LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "  		LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                              "
            +  "  	WHERE                                                                                               "
//            +  "  		t.type = '"+ DictConstant.DICT_B_OUT_TYPE_LL +"'                                                "
            +  "  		AND t.`status` = '2'                                                                            "
            +  "  		AND t1.goods_code = #{p1.p00000128Vo.column_one}                                                            "
            +  "  		AND t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                           "
            +  "  		AND t2.`enable` = '1'                                                                           "
            +  "  	GROUP BY                                                                                            "
            +  "  		t.warehouse_id,                                                                                 "
            +  "  		DATE_FORMAT( t.e_dt, '%Y%m%d' )                                                                 "
            +  "  	) tab3 ON tab1.warehouse_id = tab3.warehouse_id                                                     "
            +  "  	AND tab1.date = tab3.date                                                                           "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		SUM( t.actual_weight ) out_qty,                                                                  "
            +  "  		DATE_FORMAT( t.e_dt, '%Y-%m-%d' ) date,                                                         "
            +  "  		t.warehouse_id                                                                                  "
            +  "  	FROM                                                                                                "
            +  "  		b_out t                                                                                         "
            +  "  		LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "  		LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                              "
            +  "  	WHERE                                                                                               "
//            +  "  		t.type IN ( '"+ DictConstant.DICT_B_OUT_STATUS_SAVED +"', '"+ DictConstant.DICT_B_OUT_TYPE_JG +"' )"
            +  "  		AND t.`status` = '2'                                                                            "
            +  "  		AND t1.goods_code = #{p1.p00000128Vo.column_one}                                                            "
            +  "  		AND t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                           "
            +  "  		AND t2.`enable` = '1'                                                                           "
            +  "  	GROUP BY                                                                                            "
            +  "  		t.warehouse_id,                                                                                 "
            +  "  		DATE_FORMAT( t.e_dt, '%Y%m%d' )                                                                 "
            +  "  	) tab4 ON tab1.warehouse_id = tab4.warehouse_id                                                     "
            +  "  	AND tab1.date = tab4.date                                                                           "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		SUM( t.qty_avaible ) inventory_qty,                                                             "
            +  "  		t.warehouse_id                                                                                  "
            +  "  	FROM                                                                                                "
            +  "  		m_inventory t                                                                                   "
            +  "  		LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "  		LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                              "
            +  "  	WHERE                                                                                               "
            +  "  		t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                               "
            +  "  		AND t2.`enable` = '1'                                                                           "
            +  "  		AND t1.goods_code = #{p1.p00000128Vo.column_one}                                                "
            +  "  	GROUP BY                                                                                            "
            +  "  	t.warehouse_id                                                                                      "
            +  "  	) tab5 ON tab1.warehouse_id = tab5.warehouse_id                                                     "
            +  "  WHERE                                                                                                 "
//            +  "   (tab2.in_qty is not null or tab3.product_qty is not null or tab4.out_qty is not null or tab5.inventory_qty is not null)"
            +  "   (ifnull(tab2.in_qty, 0) != 0 or ifnull(tab3.product_qty, 0) != 0 or ifnull(tab4.out_qty, 0) != 0 or ifnull(tab5.inventory_qty, 0) != 0)"
    )
    void insertR_product_daily_a_22(@Param("p1") BProductDailyVo vo);

    @Select(" <script>                                                                                                  "
            + " ${p1.params.dataScopeAnnotation_with}                                                                                               "
            + "  SELECT                                                                                                 "
            + "    tab2.date,                                                                                           "
            + "     CONCAT(IFNULL(t2.id,'-'),'_', IFNULL(t3.id, '-'), '_', ifnull(t4.id, '-'),'_', ifnull(t5.id, '-')) id,"
            + "     IF(t3.in_qty IS NULL,'-','脱壳') product_type,                                                       "
            + "  	t7.`short_name` warehouse_name,                                                                     "
            + "  	t2.in_qty a_in_qty,                                                                                 "
            + "  	t2.product_qty  a_product_qty,                                                                      "
            + "  	t2.out_qty a_out_qty,                                                                                "
            + "  	t2.inventory_qty a_inventory_qty,                                                                   "
            + "  	t3.in_qty b_in_qty,                                                                                 "
            + "  	t3.cost_qty b_cost_qty,                                                                             "
            + "  	t3.out_qty b_out_qty,                                                                               "
            + "  	t3.inventory_qty b_inventory_qty,                                                                   "
            + "  	t4.in_qty c_in_qty,                                                                                 "
            + "  	t4.cost_qty c_cost_qty,                                                                             "
            + "  	t4.router,                                                                                          "
            + "  	t4.inventory_qty c_inventory_qty,                                                                   "
            + "  	t5.in_qty d_in_qty,                                                                                 "
            + "  	t5.out_qty d_out_qty,                                                                               "
            + "  	t5.residue_qty d_residue_qty,                                                                       "
            + "  	t5.loss_qty,                                                                                        "
            + "  	t6.in_qty e_in_qty,                                                                                 "
            + "  	t6.out_qty e_out_qty,                                                                               "
            + "  	t6.residue_qty e_residue_qty                                                                        "
            + "  FROM                                                                                                   "
            + "  	(                                                                                                   "
            + "    SELECT                                                                                               "
            + "      tab1.warehouse_id,                                                                                 "
            + "      tab1.date                                                                                          "
            + "    FROM (                                                                                               "
            + "  	SELECT                                                                                              "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	FROM                                                                                                "
            + "  		r_product_daily_a                                                                               "
            + "  	GROUP BY                                                                                            "
            + "  		warehouse_id,                                                                                   "
            + "  		date UNION                                                                                      "
            + "  	SELECT                                                                                              "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	FROM                                                                                                "
            + "  		r_product_daily_b                                                                               "
            + "  	GROUP BY                                                                                            "
            + "  		warehouse_id,                                                                                   "
            + "  		date UNION                                                                                      "
            + "  	SELECT                                                                                              "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	FROM                                                                                                "
            + "  		r_product_daily_c                                                                               "
            + "  	GROUP BY                                                                                            "
            + "  		warehouse_id,                                                                                   "
            + "  		date UNION                                                                                      "
            + "  	SELECT                                                                                              "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	FROM                                                                                                "
            + "  		r_product_daily_d                                                                               "
            + "  	GROUP BY                                                                                            "
            + "  		warehouse_id,                                                                                   "
            + "  		date UNION                                                                                      "
            + "  	SELECT                                                                                              "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	FROM                                                                                                "
            + "  		r_product_daily_e                                                                               "
            + "  	GROUP BY                                                                                            "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	) tab1                                                                                              "
            + "     WHERE (date_format(tab1.date, '%Y-%m-%d') &gt;= #{p1.init_time} or #{p1.init_time} is null)         "
            + "     AND (date_format(tab1.date, '%Y-%m-%d') &lt;= #{p1.end_time} or #{p1.end_time} is null)             "
            + "     AND (date_format(tab1.date, '%Y-%m-%d') &gt;= #{p1.batch} or #{p1.batch} is null)                   "
            + "     <if test='p1.warehouse_ids != null and p1.warehouse_ids.size != 0'>                                 "
            + "       and tab1.warehouse_id in"
            + "       <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>"
            + "         #{item}                                                                                         "
            + "       </foreach>                                                                                        "
            + "    </if>                                                                                                "
            + "       ) tab2                                                                                            "
            + "  	LEFT JOIN r_product_daily_a t2 ON tab2.warehouse_id = t2.warehouse_id                               "
            + "  	AND tab2.date = t2.date                                                                             "
            + "  	LEFT JOIN r_product_daily_b t3 ON tab2.warehouse_id = t3.warehouse_id                               "
            + "  	AND tab2.date = t3.date                                                                             "
            + "  	LEFT JOIN r_product_daily_c t4 ON tab2.warehouse_id = t4.warehouse_id                               "
            + "  	AND tab2.date = t4.date                                                                             "
            + "  	LEFT JOIN r_product_daily_d t5 ON tab2.warehouse_id = t5.warehouse_id                               "
            + "  	AND tab2.date = t5.date                                                                             "
            + "  	LEFT JOIN r_product_daily_e t6 ON tab2.warehouse_id = t6.warehouse_id                               "
            + "  	AND tab2.date = t6.date                                                                             "
            + "  	LEFT JOIN m_warehouse t7 ON tab2.warehouse_id = t7.id                                               "
            + "  	WHERE (t2.date IS NOT NULL OR t3.date IS NOT NULL OR t4.date IS NOT NULL OR t5.date IS NOT NULL OR t6.date IS NOT NULL)"
            + "     ${p1.params.dataScopeAnnotation}                                                                    "
            + "  </script>"
    )
    IPage<BProductDailyVo> selectPageList(Page<BProductDailyVo> page,@Param("p1") BProductDailyVo param);

    @Select(" <script>                                                                                                  "
            + "  SELECT                                                                                                 "
            + "     tab2.date,                                                                                          "
            + "     IF(t3.in_qty IS NULL,'-','脱壳') product_type,                                                       "
            + "     @row_num:= @row_num+ 1 as no,                                                                       "
            + "  	t7.`short_name` warehouse_name,                                                                     "
            + "  	t2.in_qty a_in_qty,                                                                                 "
            + "  	t2.product_qty  a_product_qty,                                                                      "
            + "  	t2.out_qty a_out_qty,                                                                               "
            + "  	t2.inventory_qty a_inventory_qty,                                                                   "
            + "  	t3.in_qty b_in_qty,                                                                                 "
            + "  	t3.cost_qty b_cost_qty,                                                                             "
            + "  	t3.out_qty b_out_qty,                                                                               "
            + "  	t3.inventory_qty b_inventory_qty,                                                                   "
            + "  	t4.in_qty c_in_qty,                                                                                 "
            + "  	t4.cost_qty c_cost_qty,                                                                             "
            + "  	t4.router,                                                                                          "
            + "  	t4.inventory_qty c_inventory_qty,                                                                   "
            + "  	t5.in_qty d_in_qty,                                                                                 "
            + "  	t5.out_qty d_out_qty,                                                                               "
            + "  	t5.residue_qty d_residue_qty,                                                                       "
            + "  	t5.loss_qty,                                                                                        "
            + "  	t6.in_qty e_in_qty,                                                                                 "
            + "  	t6.out_qty e_out_qty,                                                                               "
            + "  	t6.residue_qty e_residue_qty                                                                        "
            + "  FROM                                                                                                   "
            + "  	(                                                                                                   "
//            + "    select                                                                                               "
//            + "      tab3.warehouse_id,                                                                                 "
//            + "      tab3.date                                                                                          "
//            + "     from(                                                                                               "
            + " ${p1.params.dataScopeAnnotation_with}                                                                   "
            + "    SELECT                                                                                               "
            + "      tab1.warehouse_id,                                                                                 "
            + "      tab1.date                                                                                          "
            + "    FROM (                                                                                               "
            + "  	SELECT                                                                                              "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	FROM                                                                                                "
            + "  		r_product_daily_a                                                                               "
            + "  	GROUP BY                                                                                            "
            + "  		warehouse_id,                                                                                   "
            + "  		date UNION                                                                                      "
            + "  	SELECT                                                                                              "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	FROM                                                                                                "
            + "  		r_product_daily_b                                                                               "
            + "  	GROUP BY                                                                                            "
            + "  		warehouse_id,                                                                                   "
            + "  		date UNION                                                                                      "
            + "  	SELECT                                                                                              "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	FROM                                                                                                "
            + "  		r_product_daily_c                                                                               "
            + "  	GROUP BY                                                                                            "
            + "  		warehouse_id,                                                                                   "
            + "  		date UNION                                                                                      "
            + "  	SELECT                                                                                              "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	FROM                                                                                                "
            + "  		r_product_daily_d                                                                               "
            + "  	GROUP BY                                                                                            "
            + "  		warehouse_id,                                                                                   "
            + "  		date UNION                                                                                      "
            + "  	SELECT                                                                                              "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	FROM                                                                                                "
            + "  		r_product_daily_e                                                                               "
            + "  	GROUP BY                                                                                            "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	) tab1                                                                                              "
            + "     WHERE (date_format(tab1.date, '%Y-%m-%d') &gt;= #{p1.init_time} or #{p1.init_time} is null)       "
            + "     AND (date_format(tab1.date, '%Y-%m-%d') &lt;= #{p1.end_time} or #{p1.end_time} is null)             "
            + "     <if test='p1.warehouse_ids != null and p1.warehouse_ids.size != 0'>                                 "
            + "       and tab1.warehouse_id in"
            + "       <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>"
            + "         #{item}                                                                                         "
            + "       </foreach>                                                                                        "
            + "    </if>                                                                                                "
            + "     ${p1.params.dataScopeAnnotation}                                                                    "
            + "     ORDER BY tab1.date DESC                                                                             "
            + "       ) tab2                                                                                            "
            + "  	LEFT JOIN r_product_daily_a t2 ON tab2.warehouse_id = t2.warehouse_id                               "
            + "  	AND tab2.date = t2.date                                                                             "
            + "  	LEFT JOIN r_product_daily_b t3 ON tab2.warehouse_id = t3.warehouse_id                               "
            + "  	AND tab2.date = t3.date                                                                             "
            + "  	LEFT JOIN r_product_daily_c t4 ON tab2.warehouse_id = t4.warehouse_id                               "
            + "  	AND tab2.date = t4.date                                                                             "
            + "  	LEFT JOIN r_product_daily_d t5 ON tab2.warehouse_id = t5.warehouse_id                               "
            + "  	AND tab2.date = t5.date                                                                             "
            + "  	LEFT JOIN r_product_daily_e t6 ON tab2.warehouse_id = t6.warehouse_id                               "
            + "  	AND tab2.date = t6.date                                                                             "
            + "  	LEFT JOIN m_warehouse t7 ON tab2.warehouse_id = t7.id                                               "
            + "     ,(select @row_num:=0) t8                                                                            "
            + "  	WHERE (t2.date IS NOT NULL OR t3.date IS NOT NULL OR t4.date IS NOT NULL OR t5.date IS NOT NULL OR t6.date IS NOT NULL)"
            + "     <if test='p1.ids != null and p1.ids.size != 0'>                                                     "
            + "       AND CONCAT(IFNULL(t2.id,'-'),'_', IFNULL(t3.id, '-'), '_', ifnull(t4.id, '-'),'_', ifnull(t5.id, '-')) in"
            + "       <foreach collection='p1.ids' item='item' index='index' open='(' separator=',' close=')'>          "
            + "         #{item}                                                                                         "
            + "       </foreach>                                                                                        "
            + "     </if>                                                                                               "
            + "  </script>"
    )
    List<BProductDailyExportVo> exportList(@Param("p1") BProductDailyVo vo);

    @Select("SELECT NOW()")
    LocalDateTime selectNowTime();

    @Delete(""
            + " DELETE FROM                                                                                             "
            + "   r_product_daily_a                                                                                     "
            + " WHERE TRUE                                                                                              "
            + " AND (date_format(date, '%Y-%m-%d') >= #{p1.init_time} or #{p1.init_time} is null or #{p1.init_time} = '')"
            + " AND (date_format(date, '%Y-%m-%d') <= #{p1.end_time} or #{p1.end_time} is null or #{p1.end_time} = '')  "
            + " AND (warehouse_id = #{p1.warehouse_id} or #{p1.warehouse_id} is null)                                   "
    )
    void deleteR_product_daily_a_201(@Param("p1") BProductDailyVo vo);

    @Insert(""
            +  "  INSERT INTO r_product_daily_a ( date, warehouse_id, in_qty, product_qty, out_qty, inventory_qty, c_time ) SELECT"
            +  "  tab1.date,                                                                                            "
            +  "  tab1.warehouse_id,                                                                                    "
            +  "  tab2.in_qty,                                                                                          "
            +  "  tab3.product_qty,                                                                                     "
            +  "  tab4.out_qty,                                                                                         "
            +  "  tab5.inventory_qty,                                                                                   "
            +  "  now()                                                                                                 "
            +  "  FROM                                                                                                  "
            +  "  	(                                                                                                   "
            +  "  	SELECT                                                                                              "
            +  "  		t.id warehouse_id,                                                                              "
            +  "  		t1.date                                                                                         "
            +  "  	FROM                                                                                                "
            +  "  		m_warehouse t,                                                                                  "
            +  "  		s_calendar t1                                                                                   "
            +  "  	WHERE TRUE                                                                                          "
//            +  "        date_format(date, '%Y%m%d') = date_format(p1.date, '%Y%m%d')                                      "
            +  "  		AND (date_format(date, '%Y-%m-%d') >= #{p1.init_time} or #{p1.init_time} is null)               "
            +  "  		AND (date_format(date, '%Y-%m-%d') <= #{p1.end_time} or #{p1.end_time} is null)                 "
            +  "        AND (t.id = #{p1.warehouse_id} or #{p1.warehouse_id} is null  )                                 "
            +  "  		AND t.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                            "
            +  "  		AND t.`enable` = '1'                                                                            "
            +  "  	) tab1                                                                                              "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		SUM( t.actual_weight ) in_qty,                                                                  "
            +  "  		t.e_dt_date date,                                                                               "
            +  "  		t.warehouse_id                                                                                  "
            +  "  	FROM                                                                                                "
            +  "  		b_in t                                                                                          "
            +  "  		LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "  		LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                              "
            +  "  	WHERE                                                                                               "
//            +  "  		t.type = '"+ DictConstant.DICT_B_IN_TYPE_JG +"'                                                 "
            +  "  		AND t.`status` = '2'                                                                            "
            +  "  		AND t1.goods_code = #{p1.p00000128Vo.column_one}                                                "
            +  "  		AND t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                           "
            +  "  		AND t2.`enable` = '1'                                                                           "
            +  "  	GROUP BY                                                                                            "
            +  "  		t.warehouse_id,                                                                                 "
            +  "  		t.e_dt_date                                                                                     "
            +  "  	) tab2 ON tab1.warehouse_id = tab2.warehouse_id                                                     "
            +  "  	AND tab1.date = tab2.date                                                                           "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		SUM( t.actual_weight ) product_qty,                                                              "
            +  "  		DATE_FORMAT( t.e_dt, '%Y-%m-%d' ) date,                                                         "
            +  "  		t.warehouse_id                                                                                  "
            +  "  	FROM                                                                                                "
            +  "  		b_out t                                                                                         "
            +  "  		LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "  		LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                              "
            +  "  	WHERE                                                                                               "
//            +  "  		t.type = '"+ DictConstant.DICT_B_OUT_TYPE_LL +"'                                                "
            +  "  		AND t.`status` = '2'                                                                            "
            +  "  		AND t1.goods_code = #{p1.p00000128Vo.column_one}                                                            "
            +  "  		AND t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                           "
            +  "  		AND t2.`enable` = '1'                                                                           "
            +  "  	GROUP BY                                                                                            "
            +  "  		t.warehouse_id,                                                                                 "
            +  "  		DATE_FORMAT( t.e_dt, '%Y%m%d' )                                                                 "
            +  "  	) tab3 ON tab1.warehouse_id = tab3.warehouse_id                                                     "
            +  "  	AND tab1.date = tab3.date                                                                           "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		SUM( t.actual_weight ) out_qty,                                                                  "
            +  "  		DATE_FORMAT( t.e_dt, '%Y-%m-%d' ) date,                                                         "
            +  "  		t.warehouse_id                                                                                  "
            +  "  	FROM                                                                                                "
            +  "  		b_out t                                                                                         "
            +  "  		LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "  		LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                              "
            +  "  	WHERE                                                                                               "
//            +  "  		t.type IN ( '"+ DictConstant.DICT_B_OUT_STATUS_SAVED +"', '"+ DictConstant.DICT_B_OUT_TYPE_JG +"' )"
            +  "  		AND t.`status` = '2'                                                                            "
            +  "  		AND t1.goods_code = #{p1.p00000128Vo.column_one}                                                            "
            +  "  		AND t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                           "
            +  "  		AND t2.`enable` = '1'                                                                           "
            +  "  	GROUP BY                                                                                            "
            +  "  		t.warehouse_id,                                                                                 "
            +  "  		DATE_FORMAT( t.e_dt, '%Y%m%d' )                                                                 "
            +  "  	) tab4 ON tab1.warehouse_id = tab4.warehouse_id                                                     "
            +  "  	AND tab1.date = tab4.date                                                                           "
            +  "  	LEFT JOIN (                                                                                         "
            +  "  	SELECT                                                                                              "
            +  "  		SUM( t.qty ) inventory_qty,                                                                     "
            +  "        DATE_FORMAT(t.dt,'%Y-%m-%d') dt,                                                                "
            +  "  		t.warehouse_id                                                                                  "
            +  "  	FROM                                                                                                "
            +  "  		b_daily_inventory t                                                                             "
            +  "  		LEFT JOIN m_goods_spec t1 ON t.sku_id = t1.id                                                   "
            +  "  		LEFT JOIN m_warehouse t2 ON t.warehouse_id = t2.id                                              "
            +  "  	WHERE                                                                                               "
            +  "  		t2.warehouse_type = '"+ DictConstant.DICT_M_WAREHOUSE_TYPE_WD +"'                               "
            +  "  		AND t2.`enable` = '1'                                                                           "
            +  "  		AND t1.goods_code = #{p1.p00000128Vo.column_one}                                                            "
            +  "  	GROUP BY                                                                                            "
            +  "  	t.warehouse_id,                                                                                     "
            +  "    DATE_FORMAT(t.dt,'%Y%m%d')                                                                          "
            +  "  	) tab5 ON tab1.warehouse_id = tab5.warehouse_id AND  tab5.dt = tab1.date                            "
            +  "  WHERE                                                                                                 "
//            +  "   (tab2.in_qty is not null or tab3.product_qty is not null or tab4.out_qty is not null or tab5.inventory_qty is not null)"
            +  "   (ifnull(tab2.in_qty, 0) != 0 or ifnull(tab3.product_qty, 0) != 0 or ifnull(tab4.out_qty, 0) != 0 or ifnull(tab5.inventory_qty, 0) != 0)"
    )
    void insertR_product_daily_a_202(@Param("p1") BProductDailyVo vo);

    @Select(""
            + " SELECT                                                                                                  "
            + "   1                                                                                                     "
            + " FROM                                                                                                    "
            + "   r_product_daily_a                                                                                     "
            + " WHERE TRUE                                                                                              "
            + " AND (date_format(date, '%Y-%m-%d') >= #{p1.init_time} or #{p1.init_time} is null or #{p1.init_time} = '')"
            + " AND (date_format(date, '%Y-%m-%d') <= #{p1.end_time} or #{p1.end_time} is null or #{p1.end_time} = '')  "
            + " AND (warehouse_id = #{p1.warehouse_id} or #{p1.warehouse_id} is null)                                   "
            + " FOR UPDATE ;                                                                                             "
    )
    List<Integer> lockR_product_daily_a_100(@Param("p1") BProductDailyVo vo);

    @Select(" <script>                                                                                                  "
            + " ${p1.params.dataScopeAnnotation_with}                                                                   "
            + "  SELECT                                                                                                 "
            + "  	ifnull(round(sum(t2.in_qty), 3), 0) a_in_qty,                                                       "
            + "  	ifnull(round(sum(t2.product_qty), 3),0)  a_product_qty,                                             "
            + "  	ifnull(round(sum(t2.out_qty), 3), 0) a_out_qty,                                                     "
            + "  	ifnull(round(sum(t3.in_qty), 3), 0) b_in_qty,                                                       "
            + "  	ifnull(round(sum(t3.cost_qty), 3), 0) b_cost_qty,                                                   "
            + "  	ifnull(round(sum(t3.out_qty), 3), 0) b_out_qty,                                                     "
            + "  	ifnull(round(sum(t4.in_qty), 3), 0) c_in_qty,                                                       "
            + "  	ifnull(round(sum(t4.cost_qty), 3), 0) c_cost_qty,                                                   "
            + "  	ifnull(round(sum(t5.in_qty), 3), 0) d_in_qty,                                                       "
            + "  	ifnull(round(sum(t5.out_qty), 3), 0) d_out_qty,                                                     "
            + "  	ifnull(round(sum(t5.loss_qty), 3), 0) loss_qty,                                                     "
            + "  	ifnull(round(sum(t6.in_qty), 3), 0) e_in_qty,                                                       "
            + "  	ifnull(round(sum(t6.out_qty), 3), 0) e_out_qty                                                      "
            + "  FROM                                                                                                   "
            + "  	(                                                                                                   "
            + "    SELECT                                                                                               "
            + "      tab1.warehouse_id,                                                                                 "
            + "      tab1.date                                                                                          "
            + "    FROM (                                                                                               "
            + "  	SELECT                                                                                              "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	FROM                                                                                                "
            + "  		r_product_daily_a                                                                               "
            + "  	GROUP BY                                                                                            "
            + "  		warehouse_id,                                                                                   "
            + "  		date UNION                                                                                      "
            + "  	SELECT                                                                                              "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	FROM                                                                                                "
            + "  		r_product_daily_b                                                                               "
            + "  	GROUP BY                                                                                            "
            + "  		warehouse_id,                                                                                   "
            + "  		date UNION                                                                                      "
            + "  	SELECT                                                                                              "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	FROM                                                                                                "
            + "  		r_product_daily_c                                                                               "
            + "  	GROUP BY                                                                                            "
            + "  		warehouse_id,                                                                                   "
            + "  		date UNION                                                                                      "
            + "  	SELECT                                                                                              "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	FROM                                                                                                "
            + "  		r_product_daily_d                                                                               "
            + "  	GROUP BY                                                                                            "
            + "  		warehouse_id,                                                                                   "
            + "  		date UNION                                                                                      "
            + "  	SELECT                                                                                              "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	FROM                                                                                                "
            + "  		r_product_daily_e                                                                               "
            + "  	GROUP BY                                                                                            "
            + "  		warehouse_id,                                                                                   "
            + "  		date                                                                                            "
            + "  	) tab1                                                                                              "
            + "     WHERE (date_format(tab1.date, '%Y-%m-%d') &gt;= #{p1.init_time} or #{p1.init_time} is null)         "
            + "     AND (date_format(tab1.date, '%Y-%m-%d') &lt;= #{p1.end_time} or #{p1.end_time} is null)             "
            + "     AND (date_format(tab1.date, '%Y-%m-%d') &gt;= #{p1.batch} or #{p1.batch} is null)                   "
            + "     <if test='p1.warehouse_ids != null and p1.warehouse_ids.size != 0'>                                 "
            + "       and tab1.warehouse_id in"
            + "       <foreach collection='p1.warehouse_ids' item='item' index='index' open='(' separator=',' close=')'>"
            + "         #{item}                                                                                         "
            + "       </foreach>                                                                                        "
            + "    </if>                                                                                                "
            + "       ) tab2                                                                                            "
            + "  	LEFT JOIN r_product_daily_a t2 ON tab2.warehouse_id = t2.warehouse_id                               "
            + "  	AND tab2.date = t2.date                                                                             "
            + "  	LEFT JOIN r_product_daily_b t3 ON tab2.warehouse_id = t3.warehouse_id                               "
            + "  	AND tab2.date = t3.date                                                                             "
            + "  	LEFT JOIN r_product_daily_c t4 ON tab2.warehouse_id = t4.warehouse_id                               "
            + "  	AND tab2.date = t4.date                                                                             "
            + "  	LEFT JOIN r_product_daily_d t5 ON tab2.warehouse_id = t5.warehouse_id                               "
            + "  	AND tab2.date = t5.date                                                                             "
            + "  	LEFT JOIN r_product_daily_e t6 ON tab2.warehouse_id = t6.warehouse_id                               "
            + "  	AND tab2.date = t6.date                                                                             "
            + "  	WHERE (t2.date IS NOT NULL OR t3.date IS NOT NULL OR t4.date IS NOT NULL OR t5.date IS NOT NULL OR t6.date IS NOT NULL)"
            + "     ${p1.params.dataScopeAnnotation}                                                                    "
            + "  </script>"
    )
    List<BProductDailyVo> selectListSumApi(@Param("p1") BProductDailyVo vo);
}
